#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
快速表结构查看器
===============

一键查看所有数据库和表的结构信息
生成详细的结构报告
"""

import sys
import os
from datetime import datetime

# 添加父目录到路径
parent_dir = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
if parent_dir not in sys.path:
    sys.path.insert(0, parent_dir)

try:
    from database_server import DatabaseServer, DatabaseConfig
    from storage_engine import CachePolicy
except ImportError as e:
    print(f"❌ 导入错误: {e}")
    print("请确保在 OperatingSystem 目录下运行此脚本")
    sys.exit(1)


class QuickSchemaViewer:
    """快速表结构查看器"""
    
    def __init__(self, db_path: str = None):
        if db_path is None:
            # 自动找到正确的demo_data路径
            current_dir = os.path.dirname(os.path.abspath(__file__))
            parent_dir = os.path.dirname(current_dir)
            db_path = os.path.join(parent_dir, "demo_data")
        self.db_path = db_path
        self.config = DatabaseConfig(
            db_path=db_path,
            cache_size=50,
            cache_policy=CachePolicy.LRU,
            enable_locking=False
        )
        
    def generate_full_report(self):
        """生成完整的数据库结构报告"""
        server = None
        try:
            print("🔍 正在生成数据库结构报告...")
            
            # 启动服务器
            server = DatabaseServer(self.config)
            server.start()
            session = server.create_session()
            
            # 生成报告
            report = []
            report.append("# 数据库系统结构报告")
            report.append("=" * 60)
            report.append(f"生成时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
            report.append(f"数据路径: {self.db_path}")
            report.append("")
            
            # 获取所有数据库
            databases = server.list_databases(session)
            
            if not databases:
                report.append("💡 系统中暂无数据库")
                print("💡 系统中暂无数据库")
                return "\n".join(report)
            
            report.append(f"## 📊 系统概览")
            report.append(f"数据库总数: {len(databases)}")
            report.append("")
            
            total_tables = 0
            total_records = 0
            
            # 遍历每个数据库
            for db_idx, db_name in enumerate(databases, 1):
                try:
                    print(f"📁 正在分析数据库: {db_name}")
                    
                    server.use_database(session, db_name)
                    db_instance = server.database_manager.get_database(db_name)
                    tables = db_instance.list_tables()
                    
                    report.append(f"## {db_idx}. 📁 数据库: {db_name}")
                    report.append("-" * 40)
                    
                    if not tables:
                        report.append("💡 此数据库中暂无表")
                        report.append("")
                        continue
                    
                    total_tables += len(tables)
                    report.append(f"表数量: {len(tables)}")
                    report.append("")
                    
                    # 遍历每个表
                    for table_idx, table_name in enumerate(tables, 1):
                        try:
                            print(f"  📋 正在分析表: {table_name}")
                            
                            # 获取表结构
                            schema = db_instance.storage_engine.table_schemas.get(table_name)
                            records = db_instance.select_records(table_name)
                            record_count = len(records)
                            total_records += record_count
                            
                            report.append(f"### {table_idx}. 📋 表: {table_name}")
                            report.append(f"记录数: {record_count}")
                            
                            if schema:
                                report.append(f"字段数: {len(schema.columns)}")
                                
                                if schema.has_primary_key():
                                    report.append(f"主键: {schema.get_primary_key()} ({schema.get_primary_key_type().name})")
                                else:
                                    report.append("主键: 无")
                                
                                report.append("")
                                report.append("字段详情:")
                                report.append("| 字段名 | 数据类型 | 长度 | 主键 | 说明 |")
                                report.append("|--------|----------|------|------|------|")
                                
                                for col in schema.columns:
                                    col_type = col.type.name
                                    col_length = str(col.length) if col.length else "-"
                                    is_pk = "是" if col.primary_key else "否"
                                    
                                    # 添加类型说明
                                    type_desc = {
                                        'INT': '整数',
                                        'VARCHAR': '可变长字符串',
                                        'CHAR': '固定长字符串', 
                                        'FLOAT': '浮点数',
                                        'BOOLEAN': '布尔值',
                                        'DATE': '日期'
                                    }.get(col_type, col_type)
                                    
                                    report.append(f"| {col.name} | {col_type} | {col_length} | {is_pk} | {type_desc} |")
                                
                                # 如果有数据，显示数据示例
                                if records and len(records) > 0:
                                    report.append("")
                                    report.append("数据示例:")
                                    
                                    # 选择前3条记录作为示例
                                    sample_records = records[:3]
                                    
                                    if sample_records:
                                        # 构建表格
                                        column_names = [col.name for col in schema.columns]
                                        
                                        # 表头
                                        header = "| " + " | ".join(column_names) + " |"
                                        separator = "|" + "|".join(["-" * (len(name) + 2) for name in column_names]) + "|"
                                        
                                        report.append(header)
                                        report.append(separator)
                                        
                                        # 数据行
                                        for record in sample_records:
                                            values = []
                                            for col_name in column_names:
                                                value = record.get(col_name)
                                                if value is None:
                                                    values.append("NULL")
                                                else:
                                                    value_str = str(value)
                                                    # 限制长度避免表格过宽
                                                    if len(value_str) > 15:
                                                        value_str = value_str[:12] + "..."
                                                    values.append(value_str)
                                            
                                            row = "| " + " | ".join(values) + " |"
                                            report.append(row)
                                        
                                        if len(records) > 3:
                                            report.append(f"... 还有 {len(records) - 3} 条记录")
                            else:
                                report.append("❌ 无法获取表结构信息")
                            
                            report.append("")
                            
                        except Exception as e:
                            report.append(f"❌ 分析表 {table_name} 时出错: {e}")
                            report.append("")
                    
                    # 数据库统计
                    try:
                        storage_stats = db_instance.get_storage_stats()
                        cache_stats = db_instance.get_cache_stats()
                        
                        report.append("### 📊 数据库统计")
                        report.append(f"- 已分配页数: {storage_stats.pages_allocated}")
                        report.append(f"- 已使用页数: {storage_stats.used_pages}")
                        report.append(f"- 缓存命中: {cache_stats.cache_hits}")
                        report.append(f"- 缓存未命中: {cache_stats.cache_misses}")
                        
                        if (cache_stats.cache_hits + cache_stats.cache_misses) > 0:
                            hit_rate = cache_stats.cache_hits / (cache_stats.cache_hits + cache_stats.cache_misses) * 100
                            report.append(f"- 缓存命中率: {hit_rate:.2f}%")
                        
                    except Exception as e:
                        report.append(f"统计信息获取失败: {e}")
                    
                    report.append("")
                    
                except Exception as e:
                    report.append(f"❌ 分析数据库 {db_name} 时出错: {e}")
                    report.append("")
            
            # 总体统计
            report.append("## 📈 总体统计")
            report.append("-" * 20)
            report.append(f"数据库总数: {len(databases)}")
            report.append(f"表总数: {total_tables}")
            report.append(f"记录总数: {total_records}")
            report.append("")
            report.append("---")
            report.append(f"报告生成完成时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
            
            print("✅ 数据库结构报告生成完成！")
            return "\n".join(report)
            
        except Exception as e:
            error_msg = f"❌ 生成报告时出错: {e}"
            print(error_msg)
            return error_msg
            
        finally:
            if server:
                server.stop()
    
    def save_report_to_file(self, filename: str = None):
        """保存报告到文件"""
        if filename is None:
            timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
            filename = f"database_structure_report_{timestamp}.md"
        
        report_content = self.generate_full_report()
        
        # 确保visual目录存在
        visual_dir = os.path.dirname(os.path.abspath(__file__))
        report_path = os.path.join(visual_dir, filename)
        
        try:
            with open(report_path, 'w', encoding='utf-8') as f:
                f.write(report_content)
            
            print(f"📝 报告已保存到: {report_path}")
            return report_path
            
        except Exception as e:
            print(f"❌ 保存报告失败: {e}")
            return None
    
    def print_summary(self):
        """打印简要摘要"""
        server = None
        try:
            print("🔍 数据库系统简要信息")
            print("=" * 40)
            
            server = DatabaseServer(self.config)
            server.start()
            session = server.create_session()
            
            databases = server.list_databases(session)
            
            if not databases:
                print("💡 系统中暂无数据库")
                return
            
            print(f"📊 数据库总数: {len(databases)}")
            print()
            
            total_tables = 0
            total_records = 0
            
            for db_name in databases:
                try:
                    server.use_database(session, db_name)
                    db_instance = server.database_manager.get_database(db_name)
                    tables = db_instance.list_tables()
                    
                    db_records = 0
                    for table_name in tables:
                        try:
                            records = db_instance.select_records(table_name)
                            db_records += len(records)
                        except:
                            pass
                    
                    total_tables += len(tables)
                    total_records += db_records
                    
                    print(f"📁 {db_name}:")
                    print(f"   表数量: {len(tables)}")
                    print(f"   记录数: {db_records}")
                    
                    if tables:
                        print(f"   表列表: {', '.join(tables)}")
                    print()
                    
                except Exception as e:
                    print(f"📁 {db_name}: ❌ 无法访问 ({e})")
                    print()
            
            print("-" * 40)
            print(f"📈 系统总计:")
            print(f"   数据库: {len(databases)} 个")
            print(f"   表: {total_tables} 个")
            print(f"   记录: {total_records} 条")
            
        except Exception as e:
            print(f"❌ 获取信息失败: {e}")
            
        finally:
            if server:
                server.stop()


def main():
    """主函数"""
    print("🔍 快速表结构查看器")
    print("=" * 50)
    
    viewer = QuickSchemaViewer()
    
    while True:
        print("\n请选择操作:")
        print("1. 显示简要信息")
        print("2. 生成详细报告并显示")
        print("3. 生成详细报告并保存到文件")
        print("4. 启动交互式查看器")
        print("0. 退出")
        
        try:
            choice = input("\n请输入选择 (0-4): ").strip()
            
            if choice == '0':
                print("👋 再见！")
                break
            elif choice == '1':
                viewer.print_summary()
            elif choice == '2':
                print("\n" + "="*60)
                report = viewer.generate_full_report()
                print("\n" + report)
            elif choice == '3':
                report_path = viewer.save_report_to_file()
                if report_path:
                    print(f"✅ 报告已保存，可以用文本编辑器打开查看: {report_path}")
            elif choice == '4':
                print("🚀 启动交互式查看器...")
                # 导入并运行交互式查看器
                from database_inspector import main as inspector_main
                inspector_main()
            else:
                print("❌ 无效选择，请输入 0-4")
                
        except KeyboardInterrupt:
            print("\n👋 程序被用户中断")
            break
        except EOFError:
            print("\n👋 程序结束")
            break
        except Exception as e:
            print(f"❌ 操作失败: {e}")


if __name__ == "__main__":
    main()
